Alde ADA 5 #1/ADA CD-ROM - Alde Publishing.iso/sql/sqldml.out

Text File | 1988-05-03 | 10.1 KB | 375 lines

-) xeq main
SELECT *
FROM   PARCEL_ACCOUNTS

93-293-02  17  821016  DAMAGE FEE  CHARGE  500.00  560.00
93-282-55   1  800101  DUES80      CHARGE  120.00  120.00
93-281-24  31  820107  DUES82      CHARGE  120.00  120.00
93-281-24  32  820107  SA7         CHARGE  240.00  360.00
93-281-24  33  820408  DUES82      CREDIT  120.00  240.00
93-281-24  34  820408  SA7         CREDIT  240.00    0.00
93-281-24  35  820809  SA8         CHARGE  115.00  115.00
93-281-24  36  821105  SA10        CHARGE   72.00  187.00
93-281-24  37  821231  PENALTY82   CHARGE   37.40  224.40
93-282-54  16  820107  DUES82      CHARGE   60.00  370.00
93-282-54  17  820107  SA7         CHARGE  240.00  610.00
93-282-54  18  821231  PENALTY82   CHARGE  122.00  732.00
93-282-55  40  820107  DUES82      CHARGE  120.00  120.00
93-282-55  41  820107  SA7         CHARGE  240.00  360.00
93-282-55  42  820203  DUES82      CREDIT  120.00  240.00
93-282-55  43  820203  SA7         CREDIT  240.00    0.00
92-291-19   7  820107  DUES82      CHARGE   50.00  190.00
92-291-19   8  820107  SA7         CHARGE  240.00  430.00
92-291-19   9  820809  SA8         CHARGE  115.00  545.00
92-291-19  10  821105  SA10        CHARGE   72.00  617.00
92-291-19  11  821123  SA3         CHARGE   42.50  659.50
92-291-19  12  821231  PENALTY82   CHARGE  131.90  791.40
92-291-44  22  820107  DUES82      CHARGE  120.00  120.00
92-291-44  23  821124  DUES82      CREDIT   60.00   60.00
92-291-44  24  821212  DUES82      CREDIT   60.00   60.00
92-293-02   4  820107  DUES82      CHARGE   60.00   60.00
92-293-02   5  820309  DUES82      CREDIT   60.00    0.00
92-293-02   6  821105  SA10        CHARGE   72.00   72.00
92-293-02   7  821119  SA10        CREDIT   72.00    0.00

SELECT *
FROM   PARCEL_ACCOUNTS
WHERE  APN = '93-281-24'

93-281-24  31  820107  DUES82      CHARGE  120.00  120.00
93-281-24  32  820107  SA7         CHARGE  240.00  360.00
93-281-24  33  820408  DUES82      CREDIT  120.00  240.00
93-281-24  34  820408  SA7         CREDIT  240.00    0.00
93-281-24  35  820809  SA8         CHARGE  115.00  115.00
93-281-24  36  821105  SA10        CHARGE   72.00  187.00
93-281-24  37  821231  PENALTY82   CHARGE   37.40  224.40

SELECT *
FROM   PARCEL_ACCOUNTS
WHERE  ENTRY = 7

92-291-19   7  820107  DUES82      CHARGE   50.00  190.00
92-293-02   7  821119  SA10        CREDIT   72.00    0.00

SELECT *
FROM   PARCEL_ACCOUNTS
WHERE  TYPE = 'CHARGE'
AND    AMOUNT = 120.0

93-282-55   1  800101  DUES80      CHARGE  120.00  120.00
93-281-24  31  820107  DUES82      CHARGE  120.00  120.00
93-282-55  40  820107  DUES82      CHARGE  120.00  120.00
92-291-44  22  820107  DUES82      CHARGE  120.00  120.00

DELETE
FROM   PARCEL_ACCOUNTS
WHERE  TYPE = 'CHARGE'

SELECT *
FROM   PARCEL_ACCOUNTS

93-281-24  33  820408  DUES82      CREDIT  120.00  240.00
93-281-24  34  820408  SA7         CREDIT  240.00    0.00
93-282-55  42  820203  DUES82      CREDIT  120.00  240.00
93-282-55  43  820203  SA7         CREDIT  240.00    0.00
92-291-44  23  821124  DUES82      CREDIT   60.00   60.00
92-291-44  24  821212  DUES82      CREDIT   60.00   60.00
92-293-02   5  820309  DUES82      CREDIT   60.00    0.00
92-293-02   7  821119  SA10        CREDIT   72.00    0.00

DELETE
FROM   PARCEL_ACCOUNTS
WHERE  APN = '93-281-24'
AND    AMOUNT = 120.0

SELECT *
FROM   PARCEL_ACCOUNTS

93-281-24  34  820408  SA7         CREDIT  240.00    0.00
93-282-55  42  820203  DUES82      CREDIT  120.00  240.00
93-282-55  43  820203  SA7         CREDIT  240.00    0.00
92-291-44  23  821124  DUES82      CREDIT   60.00   60.00
92-291-44  24  821212  DUES82      CREDIT   60.00   60.00
92-293-02   5  820309  DUES82      CREDIT   60.00    0.00
92-293-02   7  821119  SA10        CREDIT   72.00    0.00

UPDATE PARCEL_ACCOUNTS
SET    DESCRIPTION = 'BIG BUCKS'
WHERE  AMOUNT = 240.0

SELECT *
FROM   PARCEL_ACCOUNTS

93-281-24  34  820408  BIG BUCKS   CREDIT  240.00    0.00
93-282-55  42  820203  DUES82      CREDIT  120.00  240.00
93-282-55  43  820203  BIG BUCKS   CREDIT  240.00    0.00
92-291-44  23  821124  DUES82      CREDIT   60.00   60.00
92-291-44  24  821212  DUES82      CREDIT   60.00   60.00
92-293-02   5  820309  DUES82      CREDIT   60.00    0.00
92-293-02   7  821119  SA10        CREDIT   72.00    0.00

UPDATE PARCEL_ACCOUNTS
SET    DESCRIPTION = 'DUES82 TOO',
       BALANCE = 0.0
WHERE  APN = '92-291-44'
AND    DATE = '821212'

SELECT *
FROM   PARCEL_ACCOUNTS

93-281-24  34  820408  BIG BUCKS   CREDIT  240.00    0.00
93-282-55  42  820203  DUES82      CREDIT  120.00  240.00
93-282-55  43  820203  BIG BUCKS   CREDIT  240.00    0.00
92-291-44  23  821124  DUES82      CREDIT   60.00   60.00
92-291-44  24  821212  DUES82 TOO  CREDIT   60.00    0.00
92-293-02   5  820309  DUES82      CREDIT   60.00    0.00
92-293-02   7  821119  SA10        CREDIT   72.00    0.00

UPDATE PARCEL_ACCOUNTS
SET    DESCRIPTION = 'OOPS'

SELECT *
FROM   PARCEL_ACCOUNTS

93-281-24  34  820408  OOPS        CREDIT  240.00    0.00
93-282-55  42  820203  OOPS        CREDIT  120.00  240.00
93-282-55  43  820203  OOPS        CREDIT  240.00    0.00
92-291-44  23  821124  OOPS        CREDIT   60.00   60.00
92-291-44  24  821212  OOPS        CREDIT   60.00    0.00
92-293-02   5  820309  OOPS        CREDIT   60.00    0.00
92-293-02   7  821119  OOPS        CREDIT   72.00    0.00

DELETE
FROM   PARCEL_ACCOUNTS

SELECT *
FROM   PARCEL_ACCOUNTS


INSERT INTO PARCEL_ACCOUNTS ( APN )
       VALUES ('55-555-55')

SELECT *
FROM   PARCEL_ACCOUNTS

55-555-55   0                                0.00    0.00

INSERT INTO PARCEL_ACCOUNTS ( ENTRY, DATE, APN )
       VALUES (99, '850411', '66-666-66')

SELECT *
FROM   PARCEL_ACCOUNTS

55-555-55   0                                0.00    0.00
66-666-66  99  850411                        0.00    0.00

SELECT *
FROM   CELLAR

SELECT *
FROM   CELLAR
WHERE  WINE = 'Chardonnay'

SELECT BIN, PRODUCER, READY, BOTTLES
FROM   CELLAR
WHERE  WINE = 'Chardonnay'

SELECT *
FROM   CELLAR
WHERE  BIN = 3

SELECT CODE
FROM   CITIES
WHERE  CITY = 'San Francisco'

SELECT CODE
FROM   CITIES
WHERE  CITY = 'Chicago'

SELECT *
FROM   FLIGHTS
WHERE  FROM_CODE = 'SFO'
AND    TO_CODE = 'ORD'

SELECT *
FROM   FLIGHTS
WHERE  FROM_CODE =
      (SELECT CODE
       FROM   CITIES
       WHERE  CITY = 'San Francisco')
AND    TO_CODE =
      (SELECT CODE
       FROM   CITIES
       WHERE  CITY = 'Chicago')

SELECT *
FROM   FLIGHTS
WHERE  FROM_CODE =
      (SELECT CODE
       FROM   CITIES
       WHERE  CITY = 'San Francisco')
AND    TO_CODE =
      (SELECT CODE
       FROM   CITIES
       WHERE  CITY = 'Chicago')
ORDER  BY DEP_TIME

SELECT OWNER
FROM   PARCELS
WHERE  APN = '93-282-55'

SELECT AMOUNT
FROM   PARCEL_ACCOUNTS
WHERE  APN = '93-282-55'
AND    DESCRIPTION = 'PENALTY81'
AND    TYPE = 'CHARGE'

SELECT *
FROM   OWNERS
WHERE  ADDRESS LIKE '%BERKELEY%'

SELECT ENTRY + 1
FROM   LAST_ENTRIES
WHERE  ACCOUNT = 'GENERAL'

SELECT *
FROM   GENERAL_LEDGER
WHERE  PARTY = 'ROAD FIXERS, INC.'
AND    TYPE = 'CHARGE'

SELECT SUM(AMOUNT)
FROM   GENERAL_LEDGER
WHERE  PARTY = 'ROAD FIXERS, INC.'
AND    TYPE = 'CHARGE'

SELECT COUNT(*)
FROM   PARCEL_ACCOUNTS
WHERE  APN = '93-282-55'
AND    TYPE = 'CREDIT'
AND    DATE > '811231'
AND    DATE < '830101'

SELECT MAX(DATE)
FROM   PARCEL_ACCOUNTS
WHERE  APN = '93-282-55'
AND    TYPE = 'CREDIT'

SELECT *
FROM   OWNERS
WHERE  OWNER =
      (SELECT OWNER
       FROM   PARCELS
       WHERE  APN = '93-282-55')

SELECT APN
FROM   PARCELS
WHERE  OWNER = 'JOHN MINSKI'

SELECT SUM(AMOUNT)
FROM   PARCEL_ACCOUNTS
WHERE  TYPE = 'CREDIT'
AND    APN IN ( '93-282-50', '93-282-51', '93-282-54', '93-282-58' )

SELECT SUM(AMOUNT)
FROM   PARCEL_ACCOUNTS
WHERE  TYPE = 'CREDIT'
AND    APN IN
      (SELECT APN
       FROM   PARCELS
       WHERE  OWNER = 'JOHN MINSKI')

SELECT SAN, EXPLANATION, APN
FROM   SPECIAL_ASSESSMENTS, PARCELS
WHERE  SPECIAL_ASSESSMENTS.ROAD = PARCELS.ROAD

SELECT PARCELS.APN, OWNERS.OWNER, OWNERS.ADDRESS, OWNERS.PHONE
FROM   PARCELS, OWNERS
WHERE  PARCELS.IMPROVED = 'Y'
AND    PARCELS.OWNER = OWNERS.OWNER
ORDER  BY OWNERS.OWNER, PARCELS.APN

SELECT PARCELS.APN, OWNERS.OWNER, OWNERS.ADDRESS, OWNERS.PHONE
FROM   PARCELS, OWNERS
WHERE  PARCELS.IMPROVED = 'Y'
AND    PARCELS.OWNER = OWNERS.OWNER
ORDER  BY OWNERS.OWNER DESC, PARCELS.APN

SELECT APN, OWNER
FROM   PARCELS
WHERE  EXISTS
      (SELECT *
       FROM   PARCEL_ACCOUNTS
       WHERE  APN = PARCELS.APN
       AND    DESCRIPTION = 'DUES82'
       AND    TYPE = 'CREDIT')

SELECT APN, OWNER
FROM   PARCELS
WHERE  NOT EXISTS
      (SELECT *
       FROM   PARCEL_ACCOUNTS
       WHERE  APN = PARCELS.APN
       AND    DESCRIPTION = 'DUES82'
       AND    TYPE = 'CREDIT')

SELECT PARTY, SUM(AMOUNT)
FROM   GENERAL_LEDGER
WHERE  TYPE = 'CHARGE'
GROUP  BY PARTY

SELECT OWNER
FROM   PARCELS
GROUP  BY OWNER
HAVING COUNT(*) > 1

SELECT PARCELS.OWNER, SUM(PARCEL_ACCOUNTS.AMOUNT)
FROM   PARCELS, PARCEL_ACCOUNTS
WHERE  PARCELS.APN = PARCEL_ACCOUNTS.APN
AND    PARCEL_ACCOUNTS.TYPE = 'CREDIT'
AND    PARCEL_ACCOUNTS.DATE LIKE '82%'
GROUP  BY PARCELS.OWNER
HAVING SUM(PARCEL_ACCOUNTS.AMOUNT) > 500
ORDER  BY PARCELS.OWNER

SELECT APN
FROM   PARCELS
WHERE  BALANCE < 0

SELECT OWNER, PHONE
FROM   OWNERS
WHERE  OWNER IN
      (SELECT OWNER
       FROM   PARCELS
       WHERE  BALANCE < 0)

SELECT AVG(AMOUNT)
FROM   GENERAL_LEDGER
WHERE  DATE LIKE '82%'
AND    TYPE = 'CREDIT'

SELECT PARCELS.APN, PARCELS.ROAD, PARCELS.OWNER, PARCEL_ACCOUNTS.DATE,
       PARCEL_ACCOUNTS.AMOUNT, PARCEL_ACCOUNTS.BALANCE
FROM   PARCELS, PARCEL_ACCOUNTS
WHERE  PARCELS.APN = PARCEL_ACCOUNTS.APN
AND    PARCELS.LAST_ENTRY = PARCEL_ACCOUNTS.ENTRY
ORDER  BY PARCELS.APN

SELECT APN, OWNER
FROM   PARCELS
WHERE  EXISTS
      (SELECT *
       FROM   PARCEL_ACCOUNTS
       WHERE  APN = PARCELS.APN
       AND    TYPE = 'CREDIT'
       AND    AMOUNT > 499.99)

SELECT APN
FROM   PARCEL_ACCOUNTS
WHERE  TYPE = 'CHARGE'
AND    DATE > '801231'
GROUP  BY APN
HAVING COUNT(*) > 5
ORDER  BY APN

-)